﻿using System;
using Excel = Microsoft.Office.Interop.Excel;

namespace CmdPrep.Backends
{
    class excel_addin
    {
        public Excel.Application xlApp;
        public Excel.Workbook XlWorkbook;
        public Excel.Worksheet XlSheet;
        public bool Rack_Col = false;
        public bool keep_open = false;
        
        public bool Open_TextFile(string FileName, string save_as)
        {
            System.Threading.Thread thisThread = System.Threading.Thread.CurrentThread;
            System.Globalization.CultureInfo origionalCulture = thisThread.CurrentCulture;
            try
            {
                thisThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                xlApp = new Excel.Application();
                object missing = Type.Missing;
                xlApp.Visible = true;
                // create the workbook object by opening the excel file.
               /* XlWorkbook = xlApp.Workbooks.Open(FileName,
                                                      0,
                                                      true,
                                                      4,
                                                      "",
                                                      "",
                                                      true,
                                                      Excel.XlPlatform.xlWindows,
                                                      ";",
                                                      false,
                                                      false,
                                                      0,
                                                      true,missing, missing);
                 */
                XlWorkbook = xlApp.Workbooks.Open(FileName
                                                  ,missing,missing,missing,missing,missing,missing,missing
                                                  ,true,Excel.XlPlatform.xlWindows
                                                  ,missing,missing,missing,missing,missing);
                // get the active worksheet using sheet name or active sheet
                XlSheet = (Excel.Worksheet)XlWorkbook.ActiveSheet;
                //int index = 0;
                // This row,column index should be changed as per your need.
                // i.e. which cell in the excel you are interesting to read.
                //object rowIndex = 2;
                //object colIndex1 = 1;
                //object colIndex2 = 2;
                try
                {
                    Excel.Range objRange;
                    objRange = XlSheet.get_Range("A1", missing);
           //         objRange.EntireRow.Insert(1, missing);
           //         objRange = XlSheet.get_Range("A1", "E1");
                    objRange.EntireRow.Font.Name = "GE Inspira Small Caps";
                    objRange.EntireRow.Font.Bold = true;
                    objRange.EntireRow.Font.Size = 12;
                    objRange.EntireRow.Font.ColorIndex = 55;

                    XlSheet.Cells[1, 1] = "SLOT#";
                    XlSheet.Cells[1, 2] = "Drawing Type";
                    XlSheet.Cells[1, 3] = "Title 3";
                    XlSheet.Cells[1, 4] = "Cabinet";
                    if (Rack_Col == true)
                    {
                        XlSheet.Cells[1, 5] = "RACK#";
                        ((Excel.Range)XlSheet.Cells[1, 5]).EntireColumn.AutoFit();
                    }
                    ((Excel.Range)XlSheet.Cells[1, 1]).EntireColumn.AutoFit();
                    ((Excel.Range)XlSheet.Cells[1, 2]).EntireColumn.AutoFit();
                    ((Excel.Range)XlSheet.Cells[1, 3]).EntireColumn.AutoFit();
                    ((Excel.Range)XlSheet.Cells[1, 4]).EntireColumn.AutoFit();

                    //while (((Excel.Range)XlSheet.Cells[rowIndex, colIndex1]).Value2 != null)
                    //{
                    //    rowIndex = 2 + index;
                    //    string firstName = ((Excel.Range)XlSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
                    //    string lastName = ((Excel.Range)XlSheet.Cells[rowIndex, colIndex2]).Value2.ToString();
                    //    //Console.WriteLine("Name : {0},{1} ", firstName, lastName);
                    //    index++;
                    //}
                }
                catch (Exception ex)
                {
                    xlApp.Quit();
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    XlWorkbook.SaveAs(save_as,
                                      Excel.XlFileFormat.xlWorkbookNormal,
                                      missing,missing,
                                      false,
                                      false,
                                      Excel.XlSaveAsAccessMode.xlNoChange,
                                      missing,
                                      missing,
                                      missing,
                                      missing,
                                      missing);
                    if (keep_open == false)
                    {
                        XlWorkbook.Close(false, missing, missing);
                        xlApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    } //if
                } //finally
            } //try
            finally
            {
                thisThread.CurrentCulture = origionalCulture;
            }
            return true;
        } //public bool Open_TextFile(string FileName, string save_as)
    } //class excel_addin
} // NameSpace
